package sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import beans.BacklogItemBean;
import beans.BacklogViewBean;
import beans.TeamMemberItemBean;
import beans.UserStoryBean;
import beans.UserStoryTypesBean;
import beans.UserStoryTypesItemBean;

public class UserStoryDao {
	private DataAccess dataAccess = new DataAccess();
	
	public UserStoryBean getUserStoryInfo(int id) throws ClassNotFoundException, SQLException {
		UserStoryBean userStoryBean = new UserStoryBean();

		ResultSet rset = dataAccess.getSelectResult("SELECT US.ID, US.PROJECT_ID, P.NAME, US.NAME, US.DESCRIPTION, US.TYPE, US.STORY_POINTS"
				+ " FROM USER_STORIES US, PROJECTS P"
				+ " WHERE US.PROJECT_ID = P.ID"
				+ " AND US.ID = " + id);
		
		while (rset.next()) {
			userStoryBean.setId(rset.getInt("US.ID"));
			userStoryBean.setProjectId(rset.getInt("US.PROJECT_ID"));
			userStoryBean.setProjectName(rset.getString("P.NAME"));
			userStoryBean.setName(rset.getString("US.NAME"));
			userStoryBean.setDescription(rset.getString("US.DESCRIPTION"));
			userStoryBean.setType(rset.getInt("US.TYPE"));
			userStoryBean.setStoryPoints(rset.getInt("US.STORY_POINTS"));
		}
		dataAccess.closeConnection();
		
		return userStoryBean;
	}
	
	public BacklogViewBean getBacklogViewInfo(int projectId) throws ClassNotFoundException, SQLException {
		BacklogViewBean backlogViewBean = null;
		
		String query = "SELECT US.ID, NAME, DESCRIPTION, TYPE, UST.TYPE_NAME, STORY_POINTS"
				+ " FROM USER_STORIES US, USER_STORY_TYPE UST"
				+ " WHERE US.TYPE = UST.ID"
				+ " AND PROJECT_ID = '" + projectId + "'"
				+ " AND SPRINT_ID is null"
				+ " ORDER BY PRIORITY";

		ResultSet rset = dataAccess.getSelectResult(query);
		
		backlogViewBean = new BacklogViewBean();
		while (rset.next()) {
			Integer storyPoints = rset.getInt("STORY_POINTS");
			if (rset.wasNull()) {
				storyPoints = null;
			}

			BacklogItemBean backlogItemBean = new BacklogItemBean();
			backlogItemBean.setUserStoryId(rset.getInt("US.ID"));
			backlogItemBean.setUserStoryName(rset.getString("NAME"));
			backlogItemBean.setUserStoryDescription(rset.getString("DESCRIPTION"));
			backlogItemBean.setUserStoryType(rset.getInt("TYPE"));
			backlogItemBean.setUserStoryTypeName(rset.getString("TYPE_NAME"));
			backlogItemBean.setUserStoryStoryPoints(storyPoints);
				
			backlogViewBean.addRow(backlogItemBean);
		}
		dataAccess.closeConnection();
		
		return backlogViewBean;		
	}

	private int getHighestPriorityInBacklog() throws ClassNotFoundException, SQLException {
		String query = "SELECT MAX(PRIORITY) from USER_STORIES WHERE SPRINT_ID IS NULL";
		int maxPriority = 0;

		ResultSet rset = dataAccess.getSelectResult(query);
		while (rset.next()) {
			maxPriority = rset.getInt(1);
		}
		// Don't call dataAccess.closeConnection() as this messes up the UserStoryDao.insert() prepared
		// statement call with a MySQLNonTransientConnectionException: No operations allowed after connection
		// closed.
		
		return maxPriority;
	}
	
	public int prioritizeBacklogItem(Connection conn, int sprintId, int itemId, int priority) throws ClassNotFoundException, SQLException {
		boolean closeIsOk = false;
		
		if (conn == null) {
			conn = dataAccess.getConnection();
			closeIsOk = true;
		}
		
		String query = "UPDATE USER_STORIES SET PRIORITY = ?, SPRINT_ID = ? WHERE ID = ?";

		PreparedStatement preparedStatement = conn.prepareStatement(query);
		
		preparedStatement.setInt(1, priority);
		if (sprintId == 0) {
			preparedStatement.setNull(2, Types.INTEGER);
		}
		else {
			preparedStatement.setInt(2, sprintId);
		}
		preparedStatement.setInt(3, itemId);
		
		int rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		
		if (closeIsOk) {
			dataAccess.closeConnection();
		}

		return rowsUpdated;
	}
	
	private String getUserStoryTypeName(int userStoryId) throws ClassNotFoundException, SQLException {
		String userStoryTypeName = "";
		
		// get user story name for screen
		
		ResultSet rset = dataAccess.getSelectResult("SELECT TYPE_NAME FROM USER_STORIES US, USER_STORY_TYPE UST"
				+ " WHERE US.TYPE = UST.ID AND US.ID =" + userStoryId);

		while (rset.next()) {
			userStoryTypeName = rset.getString("TYPE_NAME");
		}
		dataAccess.closeConnection();
		
		return userStoryTypeName;
	}

	public int insert(TeamMemberItemBean currentUserInfo, UserStoryBean userStoryBean) throws ClassNotFoundException, SQLException {
		String query = "INSERT INTO USER_STORIES (PROJECT_ID, NAME, DESCRIPTION, TYPE, STORY_POINTS, PRIORITY) VALUES (?, ?, ?, ?, ?, ?)";
				
		int maxPriority = getHighestPriorityInBacklog();
		
		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setInt(1, currentUserInfo.getProjectId());
		preparedStatement.setString(2, userStoryBean.getName());
		preparedStatement.setString(3, userStoryBean.getDescription());
		preparedStatement.setInt(4, userStoryBean.getType());
		if (userStoryBean.getStoryPoints() == null) {
			preparedStatement.setNull(5, Types.INTEGER);
		}
		else {
			preparedStatement.setInt(5, userStoryBean.getStoryPoints());
		}
		preparedStatement.setInt(6, maxPriority + 1);

		int rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		if (rowsUpdated == 1) {
			// get id of new data
			ResultSet rset = dataAccess.getSelectResult("SELECT LAST_INSERT_ID()");
			while (rset.next()) {
				userStoryBean.setId(rset.getInt(1));
			}
			rset.close();
			
			// return data

			userStoryBean.setProjectId(currentUserInfo.getProjectId());
			userStoryBean.setProjectName(currentUserInfo.getProjectName());

			// get user story name for screen
			userStoryBean.setTypeName(getUserStoryTypeName(userStoryBean.getId()));
		}
		dataAccess.closeConnection();
			
		return rowsUpdated;
	}
	
	public int update(UserStoryBean userStoryBean) throws ClassNotFoundException, SQLException {
		String query = "UPDATE USER_STORIES SET NAME = ?, DESCRIPTION = ?, TYPE = ?, STORY_POINTS = ?"
				+ " WHERE ID = ?";

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setString(1, userStoryBean.getName());
		preparedStatement.setString(2, userStoryBean.getDescription());
		preparedStatement.setInt(3, userStoryBean.getType());
		Integer storyPoints = userStoryBean.getStoryPoints();
		if (storyPoints == null || storyPoints == 0) {
			preparedStatement.setNull(4, Types.INTEGER);
		}
		else {
			preparedStatement.setInt(4, storyPoints);
		}
		preparedStatement.setInt(5, userStoryBean.getId());
		
		int rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		if (rowsUpdated == 1) {
			userStoryBean.setTypeName(getUserStoryTypeName(userStoryBean.getId()));
		}
		dataAccess.closeConnection();

		return rowsUpdated;
	}
	
	public int delete(int id) throws ClassNotFoundException, SQLException {
		String query = "DELETE FROM USER_STORIES WHERE ID = ?";
		int rowsUpdated = 0;

		Connection conn = dataAccess.getConnection();
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setInt(1, id);
		
		rowsUpdated = dataAccess.executeUpdate(preparedStatement);
		dataAccess.closeConnection();
			
		return rowsUpdated;
	}

	public UserStoryTypesBean getUserStoryTypes() throws ClassNotFoundException, SQLException {
		UserStoryTypesBean userStoryTypesBean = new UserStoryTypesBean();
		
		String query = "SELECT ID, TYPE_NAME FROM USER_STORY_TYPE ORDER BY ID";

		ResultSet rset;
		rset = dataAccess.getSelectResult(query);
	
		while (rset.next()) {
			UserStoryTypesItemBean userStoryTypesItemBean = new UserStoryTypesItemBean();
			userStoryTypesItemBean.setId(rset.getInt("ID"));
			userStoryTypesItemBean.setTypeName(rset.getString("TYPE_NAME"));
			userStoryTypesBean.addRow(userStoryTypesItemBean);
		}
		dataAccess.closeConnection();
		
		return userStoryTypesBean;
	}
}
